package accessDBProj;

import java.sql.*;
import java.util.Map;
import java.util.Properties;
import java.util.Vector;

public class AccessDAO {
	private String path = "MedicineCase.mdb";

	private String pwd = "medicMg";

	public Connection conn = null;

	/**
	 * 连接未加密的数据库
	 * 
	 * @param dbPath
	 * @return
	 * @throws Exception
	 */
	public Statement getStatement(String dbPath) throws Exception {
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String dburl = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};"
				+ "DBQ=" + dbPath;// 此为NO-DSN方式
		// String dburl ="jdbc:odbc:odbcName";//此为ODBC连接方式
		conn = DriverManager.getConnection(dburl);
		return conn.createStatement();
	}

	/**
	 * 连接加密的数据库
	 * 
	 * @param dbPath
	 * @return
	 * @throws Exception
	 */
	public Statement getStatement(String dbPath, String password)
			throws Exception {
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String dburl = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};"
				+ "pwd=" + password + ";DBQ=" + dbPath;// 此为NO-DSN方式
		// String dburl ="jdbc:odbc:odbcName";//此为ODBC连接方式
		conn = DriverManager.getConnection(dburl);
		return conn.createStatement();
	}

	/**
	 * 连接加密的可回滚型数据库
	 * 
	 * @param dbPath
	 * @return
	 * @throws Exception
	 */
	public Statement getStatementRoll(String dbPath, String password)
			throws Exception {
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String dburl = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};"
				+ "pwd=" + password + ";DBQ=" + dbPath;// 此为NO-DSN方式
		// String dburl ="jdbc:odbc:odbcName";//此为ODBC连接方式
		// 国际化 乱码解决方案
		/*
		 * 
		 * Properties charSet = new Properties(); charSet.getProperty("charSet",
		 * "unicode"); conn = DriverManager.getConnection(dburl, charSet);
		 */
		conn = DriverManager.getConnection(dburl);
		return conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
	}

	/**
	 * 查询数据库
	 * 
	 * @param stmt
	 * @param query
	 * @return
	 * @throws Exception
	 */
	public ResultSet executeQuery(Statement stmt, String query)
			throws Exception {
		ResultSet rs = stmt.executeQuery(query);
		return rs;
	}

	/**
	 * 更新数据库
	 * 
	 * @param stmt
	 * @param query
	 * @throws SQLException
	 */
	public void executeUpdate(Statement stmt, String query) throws SQLException {
		stmt.executeUpdate(query);
	}

	/**
	 * 关闭链接
	 * 
	 * @throws SQLException
	 */
	public void close() throws SQLException {
		if (conn != null)
			conn.close();
	}

	// ///////////////////////////////////////////////////////////
	public boolean addRecord(Map row) {
		StringBuffer check_sql = new StringBuffer(
				"select 1 from patients where m_id=");
		StringBuffer insert_sql = new StringBuffer(
				"insert into patients (M_ID,M_NM,M_AGE,M_SEX,M_DIABE,M_DIA_AGE,M_LEF_EYE,M_RIG_EYE,M_TEL,M_ADR,M_CURE_HIS,M_CURE_FIELD) "
						+ " values (");
		ResultSet rs = null;
		check_sql.append("'" + row.get("jTF_ID") + "'");
		try {
			Statement stmt = getStatement(path, pwd);
			rs = executeQuery(stmt, check_sql.toString());
			if (rs.next()) { // M_ID的记录是否已经存在
				rs.close();
				return false;
			}
			rs.close();
			insert_sql.append("'" + row.get("jTF_ID") + "',");
			insert_sql.append("'" + row.get("jTF_NM") + "',");
			insert_sql.append("'" + row.get("jTF_AGE") + "',");
			insert_sql.append("'" + row.get("jRa_man") + "',");
			insert_sql.append("'" + row.get("jCB_DIABE") + "',");
			insert_sql.append("'" + row.get("jT_DIABE") + "',");
			insert_sql.append("'" + row.get("jTF_RIG_EYE") + "',");
			insert_sql.append("'" + row.get("jTF_LEF_EYE") + "',");
			insert_sql.append("'" + row.get("jTF_TEL") + "',");
			insert_sql.append("'" + row.get("jTF_ADR") + "',");
			insert_sql.append("'" + row.get("jTA_CURE_HIS") + "',");
			insert_sql.append("'" + row.get("jTA_CURE_FIELD") + "')");
			executeUpdate(stmt, insert_sql.toString());
			//

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return true;
	}

	/*
	 * 数据查询
	 */
	public Vector search(Map map_info) {
		StringBuffer sql = new StringBuffer("select * from patients ");
		int tag_search = 0;
		String tmp = (String) map_info.get("jTF_ID");
		if (!tmp.equals("")) {
			tag_search++;
			if (tag_search == 1)
				sql.append("where M_ID like '%").append(tmp + "%'");
			else
				sql.append("and M_ID like '").append(tmp + "%'");
		}
		tmp = (String) map_info.get("jTF_NM");
		if (!tmp.equals("")) {
			tag_search++;
			if (tag_search == 1)
				sql.append("where M_NM like '%").append(tmp + "%'");
			else
				sql.append("and M_NM like '").append(tmp + "%'");
		}
		tmp = (String) map_info.get("jTF_AGE");
		if (!tmp.equals("")) {
			tag_search++;
			if (tag_search == 1)
				sql.append("where M_AGE like '%").append(tmp + "%'");
			else
				sql.append("and M_AGE like '").append(tmp + "%'");
		}
		ResultSet rs = null;
		Vector rows = new Vector();
		try {
			Statement stmt = getStatement(path, pwd);
			rs = executeQuery(stmt, sql.toString());
			while (rs.next()) {
				Vector row = new Vector();
				row.addElement(rs.getString("M_ID"));
				row.addElement(rs.getString("M_NM"));
				row.addElement(rs.getString("M_AGE"));
				row.addElement(rs.getInt("M_SEX") == 1 ? "男" : "女");
				row.addElement(rs.getInt("M_DIABE") == 1 ? "有" : "无");
				row.addElement(rs.getString("M_DIA_AGE"));
				row.addElement(rs.getString("M_LEF_EYE"));
				row.addElement(rs.getString("M_RIG_EYE"));
				row.addElement(rs.getString("M_TEL"));
				row.addElement(rs.getString("M_ADR"));
				row.addElement(rs.getString("M_CURE_HIS"));
				row.addElement(rs.getString("M_CURE_FIELD"));
				rows.addElement(row);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rows;
	}

	/*
	 * // DAO test public static void main(String args[]) throws Exception {
	 * AccessDAO db = new AccessDAO(); String dbPath = "H:\\MedicineCase.mdb";
	 * String query = "select * from patients"; String password = "medicMg";
	 * Statement stmt = db.getStatementRoll(dbPath, password); ResultSet rs =
	 * db.executeQuery(stmt, query); while (rs.next()) { //
	 * System.out.println(new String(rs.getString(2).getBytes("unicode"), //
	 * "MS932")); // System.out.println(new
	 * String(rs.getString(2).getBytes("MS932"), // "unicode"));
	 * System.out.println(new String(rs.getString(2).getBytes("unicode"),
	 * "unicode")); System.out.println(new
	 * String(rs.getString(2).getBytes("MS932"),"MS932")); //
	 * System.out.println(rs.getString(2)); } }
	 */

	public void update(Map row) {
		// TODO 自動生成されたメソッド・スタブ
		StringBuffer update_sql = new StringBuffer("update patients set ");
		// (M_ID,M_NM,M_AGE,M_SEX,M_DIABE,M_LEF_EYE,M_RIG_EYE,M_TEL,M_ADR,M_CURE_HIS,M_CURE_FIELD)
		// " +
		// " values (");
		try {
			Statement stmt = getStatement(path, pwd);
			update_sql.append("M_NM='" + row.get("jTF_NM") + "',");
			update_sql.append("M_AGE='" + row.get("jTF_AGE") + "',");
			update_sql.append("M_SEX='" + row.get("jRa_man") + "',");
			update_sql.append("M_DIABE='" + row.get("jCB_DIABE") + "',");
			update_sql.append("M_DIA_AGE='" + row.get("jT_DIABE") + "',");
			update_sql.append("M_LEF_EYE='" + row.get("jTF_RIG_EYE") + "',");
			update_sql.append("M_RIG_EYE='" + row.get("jTF_LEF_EYE") + "',");
			update_sql.append("M_TEL='" + row.get("jTF_TEL") + "',");
			update_sql.append("M_ADR='" + row.get("jTF_ADR") + "',");
			update_sql.append("M_CURE_HIS='" + row.get("jTA_CURE_HIS") + "',");
			update_sql.append("M_CURE_FIELD='" + row.get("jTA_CURE_FIELD")
					+ "'");
			update_sql.append(" WHERE M_ID='" + row.get("jTF_ID") + "'");
			executeUpdate(stmt, update_sql.toString());
			//

		} catch (Exception e) {
			// TODO 自動生成された catch ブロック
			e.printStackTrace();
		} finally {
			try {
				close();
			} catch (SQLException e) {
				// TODO 自動生成された catch ブロック
				e.printStackTrace();
			}
		}
	}

	public boolean delete(String mid) {
		String delete_sql = "delete from  patients where M_ID='" + mid + "'";
		String check_sql = "select 1 from patients where M_ID='" + mid + "'";
		try {
			Statement stmt = getStatement(path, pwd);
			ResultSet rs = executeQuery(stmt, check_sql.toString());
			if (!rs.next()) { // M_ID的记录是否存在
				rs.close();
				return false;
			}
			rs.close();
			executeUpdate(stmt, delete_sql.toString());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return true;
	}

	// 删除多行
	public void delete(Vector del_rows) {
		try {
		for(int i=0;i<del_rows.size();i++){
			String delete_sql = "delete from  patients where M_ID='" + ((Vector)del_rows.get(i)).get(0) + "'";
			Statement stmt = getStatement(path, pwd);
			executeUpdate(stmt, delete_sql.toString());
		}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
